USE [BMIG_MENSUAL_POST_CADENA]

 -- creating the store procedure
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = 'WASP_M0009_INGRESO_PRODUCCION'
	   AND 	  type = 'P') 
	DROP PROCEDURE [dbo].[WASP_M0009_INGRESO_PRODUCCION]
GO
CREATE PROCEDURE [dbo].[WASP_M0009_INGRESO_PRODUCCION] 
(@FECHA_INICIO CHAR(8), @FECHA_TERMINO AS CHAR(8)) AS
begin
    SELECT DISTINCT 
        'D' AS TipoRegistro
        , NULL AS PeriodoPro
        , NULL AS FechaEnvio
        , 1 AS NroCorrelativoArchivo
        , 1 AS TipoMovimiento
        , g.COD_SEg AS NroProducto
        , NULL AS NroPoliza
        , a.aosuc AS CODAGEN
        , a.aooper AS NroCredito
        , SUBSTRING(c.pfndoc, 1, LEN(c.pfndoc) - 1) AS RutAsegurado
        , SUBSTRING(c.pfndoc, LEN(c.pfndoc), 1) AS [DV Rut Asegurado]
        , CAST(RTRIM(LTRIM(c.pfape1)) + ' ' + RTRIM(LTRIM(c.pfape2)) + ' ' + RTRIM(LTRIM(c.pfnom1)) + ' ' + RTRIM(LTRIM(c.pfnom2)) AS varchar(50)) AS [Nombre Asegurado], e.docall + CAST(e.donro AS varchar) AS DireccionAsegurado
        , f.tel1 AS TelefonoAsegurado
        , d.z07310COM AS CodigoComuna
        , NULL AS DescripcionComuna
        , NULL AS CodigoCiudad
        , NULL AS DescripcionCuidad
        , d.z07339REG AS CodigoRegion
        , NULL AS DescripcionRegion
        , CONVERT(varchar, a.aofval, 112) AS VigenciaDesde
        , CONVERT(varchar, DATEADD(day, - 1, DATEADD(year, 1, a.aofval)), 112) AS VigenciaHasta
        , NULL AS CuotasPactadas
        , NULL AS PrimaNetaCaja
        , g.val_seg AS PrimaBrutaCaja
    into	dbo.ing_produccion_temp_ant        
    FROM    dbo.fsd010 AS a   WITH (nolock)
            INNER JOIN  dbo.ingreso_produccion_seguro_asisten_ant AS g ON a.aocta = g.ppcta
                    AND a.aooper = g.ppoper 
                    AND a.aosuc = g.ppsuc 
            INNER JOIN dbo.JT73109 AS h  WITH (nolock) ON a.aooper = h.JT73109FPA 
                    AND a.aosuc = h.JT73109SUC 
            LEFT OUTER JOIN dbo.fsd002  AS c WITH (nolock) ON a.aocta = SUBSTRING(c.pfndoc, 1, LEN(pfndoc) - 1) 
            LEFT OUTER JOIN dbo.z07331  AS d WITH (nolock) ON a.aocta = d.z07331cta 
            LEFT OUTER JOIN dbo.fsd006  AS e WITH (nolock) ON a.aocta = e.ctnro 
            LEFT OUTER JOIN dbo.ingreso_produccion_numero_telefono_ant AS f ON a.aocta = f.ctnro
    WHERE (a.aooper <> 99999999) 
            AND (a.aocta <> 999999999) 
            AND (a.aosbop = 0) 
            AND (pfndoc <> '') 
            AND (h.JT73109FAP BETWEEN @FECHA_INICIO AND @FECHA_TERMINO) 
            AND (NOT EXISTS (SELECT *
                             FROM as_desistidos AS anu
                             WHERE a.aocta = anu.hcta 
                                AND a.aooper = anu.hoper 
                                AND a.aosuc = anu.hsucur)) 
            AND (c.pfndoc <> '*********8')
             
END             
    
